﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using PS.DBUtility;
namespace PS.DAL
{
    //个人绩效考核表
    public partial class EmpKpiData
    {
         
        public bool Exists(int EmpKpiDataID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from EmpKpiData");      
            strSql.Append(" where ");
            strSql.Append(" EmpKpiDataID = @EmpKpiDataID  ");
            SqlParameter[] parameters = {
					new SqlParameter("@EmpKpiDataID", SqlDbType.Int,4)
};
            parameters[0].Value = EmpKpiDataID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }



        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(PS.Model.EmpKpiData model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into EmpKpiData(");
            strSql.Append("EmpKpiConfigID,KpiUserID,KpiUser,KpiUserDep,KpiType,KpiFile,KpiScore,CreatorID,Creator,CreatorDep,CreateTime,Editor,EditTime,DispOrder,ValidMark,Spare1,Spare2,Spare3,Spare4,SpareDecimal5,SpareDecimal6,SpareDecimal7,SpareDecimal8");
            strSql.Append(") values (");
            strSql.Append("@EmpKpiConfigID,@KpiUserID,@KpiUser,@KpiUserDep,@KpiType,@KpiFile,@KpiScore,@CreatorID,@Creator,@CreatorDep,@CreateTime,@Editor,@EditTime,@DispOrder,@ValidMark,@Spare1,@Spare2,@Spare3,@Spare4,@SpareDecimal5,@SpareDecimal6,@SpareDecimal7,@SpareDecimal8");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@EmpKpiConfigID", SqlDbType.Int,4) ,            
                        new SqlParameter("@KpiUserID", SqlDbType.Int,4) ,            
                        new SqlParameter("@KpiUser", SqlDbType.VarChar,100) ,            
                        new SqlParameter("@KpiUserDep", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@KpiType", SqlDbType.VarChar,10) ,            
                        new SqlParameter("@KpiFile", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@KpiScore", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@CreatorID", SqlDbType.Int,4) ,            
                        new SqlParameter("@Creator", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@CreatorDep", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@CreateTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@Editor", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@EditTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@DispOrder", SqlDbType.Int,4) ,            
                        new SqlParameter("@ValidMark", SqlDbType.Int,4) ,            
                        new SqlParameter("@Spare1", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@Spare2", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@Spare3", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@Spare4", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@SpareDecimal5", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@SpareDecimal6", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@SpareDecimal7", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@SpareDecimal8", SqlDbType.Decimal,9)             
              
            };

            parameters[0].Value = model.EmpKpiConfigID;
            parameters[1].Value = model.KpiUserID;
            parameters[2].Value = model.KpiUser;
            parameters[3].Value = model.KpiUserDep;
            parameters[4].Value = model.KpiType;
            parameters[5].Value = model.KpiFile;
            parameters[6].Value = model.KpiScore;
            parameters[7].Value = model.CreatorID;
            parameters[8].Value = model.Creator;
            parameters[9].Value = model.CreatorDep;
            parameters[10].Value = model.CreateTime;
            parameters[11].Value = model.Editor;
            parameters[12].Value = model.EditTime;
            parameters[13].Value = model.DispOrder;
            parameters[14].Value = model.ValidMark;
            parameters[15].Value = model.Spare1;
            parameters[16].Value = model.Spare2;
            parameters[17].Value = model.Spare3;
            parameters[18].Value = model.Spare4;
            parameters[19].Value = model.SpareDecimal5;
            parameters[20].Value = model.SpareDecimal6;
            parameters[21].Value = model.SpareDecimal7;
            parameters[22].Value = model.SpareDecimal8;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {

                return Convert.ToInt32(obj);

            }

        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(PS.Model.EmpKpiData model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update EmpKpiData set ");

            strSql.Append(" EmpKpiConfigID = @EmpKpiConfigID , ");
            strSql.Append(" KpiUserID = @KpiUserID , ");
            strSql.Append(" KpiUser = @KpiUser , ");
            strSql.Append(" KpiUserDep = @KpiUserDep , ");
            strSql.Append(" KpiType = @KpiType , ");
            strSql.Append(" KpiFile = @KpiFile , ");
            strSql.Append(" KpiScore = @KpiScore , ");
            strSql.Append(" CreatorID = @CreatorID , ");
            strSql.Append(" Creator = @Creator , ");
            strSql.Append(" CreatorDep = @CreatorDep , ");
            strSql.Append(" CreateTime = @CreateTime , ");
            strSql.Append(" Editor = @Editor , ");
            strSql.Append(" EditTime = @EditTime , ");
            strSql.Append(" DispOrder = @DispOrder , ");
            strSql.Append(" ValidMark = @ValidMark , ");
            strSql.Append(" Spare1 = @Spare1 , ");
            strSql.Append(" Spare2 = @Spare2 , ");
            strSql.Append(" Spare3 = @Spare3 , ");
            strSql.Append(" Spare4 = @Spare4 , ");
            strSql.Append(" SpareDecimal5 = @SpareDecimal5 , ");
            strSql.Append(" SpareDecimal6 = @SpareDecimal6 , ");
            strSql.Append(" SpareDecimal7 = @SpareDecimal7 , ");
            strSql.Append(" SpareDecimal8 = @SpareDecimal8  ");
            strSql.Append(" where EmpKpiDataID=@EmpKpiDataID ");

            SqlParameter[] parameters = {
			            new SqlParameter("@EmpKpiDataID", SqlDbType.Int,4) ,            
                        new SqlParameter("@EmpKpiConfigID", SqlDbType.Int,4) ,            
                        new SqlParameter("@KpiUserID", SqlDbType.Int,4) ,            
                        new SqlParameter("@KpiUser", SqlDbType.VarChar,100) ,            
                        new SqlParameter("@KpiUserDep", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@KpiType", SqlDbType.VarChar,10) ,            
                        new SqlParameter("@KpiFile", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@KpiScore", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@CreatorID", SqlDbType.Int,4) ,            
                        new SqlParameter("@Creator", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@CreatorDep", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@CreateTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@Editor", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@EditTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@DispOrder", SqlDbType.Int,4) ,            
                        new SqlParameter("@ValidMark", SqlDbType.Int,4) ,            
                        new SqlParameter("@Spare1", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@Spare2", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@Spare3", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@Spare4", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@SpareDecimal5", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@SpareDecimal6", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@SpareDecimal7", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@SpareDecimal8", SqlDbType.Decimal,9)             
              
            };

            parameters[0].Value = model.EmpKpiDataID;
            parameters[1].Value = model.EmpKpiConfigID;
            parameters[2].Value = model.KpiUserID;
            parameters[3].Value = model.KpiUser;
            parameters[4].Value = model.KpiUserDep;
            parameters[5].Value = model.KpiType;
            parameters[6].Value = model.KpiFile;
            parameters[7].Value = model.KpiScore;
            parameters[8].Value = model.CreatorID;
            parameters[9].Value = model.Creator;
            parameters[10].Value = model.CreatorDep;
            parameters[11].Value = model.CreateTime;
            parameters[12].Value = model.Editor;
            parameters[13].Value = model.EditTime;
            parameters[14].Value = model.DispOrder;
            parameters[15].Value = model.ValidMark;
            parameters[16].Value = model.Spare1;
            parameters[17].Value = model.Spare2;
            parameters[18].Value = model.Spare3;
            parameters[19].Value = model.Spare4;
            parameters[20].Value = model.SpareDecimal5;
            parameters[21].Value = model.SpareDecimal6;
            parameters[22].Value = model.SpareDecimal7;
            parameters[23].Value = model.SpareDecimal8;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int EmpKpiDataID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from EmpKpiData ");
            strSql.Append(" where EmpKpiDataID=@EmpKpiDataID");
            SqlParameter[] parameters = {
					new SqlParameter("@EmpKpiDataID", SqlDbType.Int,4)
};
            parameters[0].Value = EmpKpiDataID;


            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool DeleteList(string EmpKpiDataIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from EmpKpiData ");
            strSql.Append(" where ID in (" + EmpKpiDataIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public PS.Model.EmpKpiData GetModel(int EmpKpiDataID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select EmpKpiDataID, EmpKpiConfigID, KpiUserID, KpiUser, KpiUserDep, KpiType, KpiFile, KpiScore, CreatorID, Creator, CreatorDep, CreateTime, Editor, EditTime, DispOrder, ValidMark, Spare1, Spare2, Spare3, Spare4, SpareDecimal5, SpareDecimal6, SpareDecimal7, SpareDecimal8  ");
            strSql.Append("  from EmpKpiData ");
            strSql.Append(" where EmpKpiDataID=@EmpKpiDataID");
            SqlParameter[] parameters = {
					new SqlParameter("@EmpKpiDataID", SqlDbType.Int,4)
};
            parameters[0].Value = EmpKpiDataID;


            PS.Model.EmpKpiData model = new PS.Model.EmpKpiData();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["EmpKpiDataID"].ToString() != "")
                {
                    model.EmpKpiDataID = int.Parse(ds.Tables[0].Rows[0]["EmpKpiDataID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["EmpKpiConfigID"].ToString() != "")
                {
                    model.EmpKpiConfigID = int.Parse(ds.Tables[0].Rows[0]["EmpKpiConfigID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["KpiUserID"].ToString() != "")
                {
                    model.KpiUserID = int.Parse(ds.Tables[0].Rows[0]["KpiUserID"].ToString());
                }
                model.KpiUser = ds.Tables[0].Rows[0]["KpiUser"].ToString();
                model.KpiUserDep = ds.Tables[0].Rows[0]["KpiUserDep"].ToString();
                model.KpiType = ds.Tables[0].Rows[0]["KpiType"].ToString();
                model.KpiFile = ds.Tables[0].Rows[0]["KpiFile"].ToString();
                if (ds.Tables[0].Rows[0]["KpiScore"].ToString() != "")
                {
                    model.KpiScore = decimal.Parse(ds.Tables[0].Rows[0]["KpiScore"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CreatorID"].ToString() != "")
                {
                    model.CreatorID = int.Parse(ds.Tables[0].Rows[0]["CreatorID"].ToString());
                }
                model.Creator = ds.Tables[0].Rows[0]["Creator"].ToString();
                model.CreatorDep = ds.Tables[0].Rows[0]["CreatorDep"].ToString();
                if (ds.Tables[0].Rows[0]["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(ds.Tables[0].Rows[0]["CreateTime"].ToString());
                }
                model.Editor = ds.Tables[0].Rows[0]["Editor"].ToString();
                if (ds.Tables[0].Rows[0]["EditTime"].ToString() != "")
                {
                    model.EditTime = DateTime.Parse(ds.Tables[0].Rows[0]["EditTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["DispOrder"].ToString() != "")
                {
                    model.DispOrder = int.Parse(ds.Tables[0].Rows[0]["DispOrder"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ValidMark"].ToString() != "")
                {
                    model.ValidMark = int.Parse(ds.Tables[0].Rows[0]["ValidMark"].ToString());
                }
                model.Spare1 = ds.Tables[0].Rows[0]["Spare1"].ToString();
                model.Spare2 = ds.Tables[0].Rows[0]["Spare2"].ToString();
                model.Spare3 = ds.Tables[0].Rows[0]["Spare3"].ToString();
                model.Spare4 = ds.Tables[0].Rows[0]["Spare4"].ToString();
                if (ds.Tables[0].Rows[0]["SpareDecimal5"].ToString() != "")
                {
                    model.SpareDecimal5 = decimal.Parse(ds.Tables[0].Rows[0]["SpareDecimal5"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SpareDecimal6"].ToString() != "")
                {
                    model.SpareDecimal6 = decimal.Parse(ds.Tables[0].Rows[0]["SpareDecimal6"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SpareDecimal7"].ToString() != "")
                {
                    model.SpareDecimal7 = decimal.Parse(ds.Tables[0].Rows[0]["SpareDecimal7"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SpareDecimal8"].ToString() != "")
                {
                    model.SpareDecimal8 = decimal.Parse(ds.Tables[0].Rows[0]["SpareDecimal8"].ToString());
                }

                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM EmpKpiData ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM EmpKpiData ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 查询月度
        /// </summary>
        /// <param name="EmpKpiType"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="empName"></param>
        /// <param name="dept"></param>
        /// <param name="orderName"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataSet getEmpKpiDataM(string isSelfOrSuperior, string EmpKpiType, string year, string month, string empName, string dept, string empid, string userid, long pageSize, long pageIndex, out long totalCount)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder sbStr = new StringBuilder();

            string strWhere = "";
            if (year != "0")
            {
                strWhere += "and year(oldEmpKpiDate)='" + year + "'";
            }
            if (month != "0")
            {
                strWhere += "and month(oldEmpKpiDate)='" + month + "'";
            }
            if (empName != "")
            {
                strWhere += "and KpiUser like '%" + empName + "%'";
            }
            if (dept != "")
            {
                strWhere += "and KpiUserDep like '%" + dept + "%'";
            }
            if (empid != "")
            {
                strWhere += "and KPIUSERID = '" + empid + "'";
            }

            if (isSelfOrSuperior == "3")
            {
                DataSet ds = new EmpPersonBase().getMyDeptPerson(userid);
                string userStr = "";
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    userStr = userStr + dr["PersonID"].ToString() + ",";
                }
                userStr = userStr.Substring(0, userStr.Length - 1);
                if (userStr != "")
                    strWhere += " and KPIUSERID in (" + userStr + ")";
                else
                    if (userid != "")
                    {
                        strWhere += "and LearderID = '" + userid + "'";
                    }
            }


            sb.Append(" select cast(row_number()  ");
            sb.Append("  over( order by KPIUSERID) ");
            sb.Append(" as int) rowindex,* from (");
            sb.Append(" SELECT * ");
            sb.Append(" FROM (SELECT DISTINCT KPIUSERID,d.EmpKpiConfigID,d.KpiUser,d.KpiUserDep,");
            sb.Append(" g.SelfMarkRate,g.LeaderMarkRate,g.EmpKpiType,convert(varchar(7),EmpKpiDate,121) EmpKpiDate ,g.EmpKpiDate oldEmpKpiDate");
            sb.Append(" FROM EmpKpiData d");
            sb.Append(" left join dbo.EmpKpiConfig g on g.EmpKpiConfigID=d.EmpKpiConfigID");
            sb.Append(" where d.ValidMark=1 ");
            sb.Append(" ) A ");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT SelfScore= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT kpiscore FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID and (year(EmpKpiDate)=year(A.oldEmpKpiDate) and month(EmpKpiDate)=month(A.oldEmpKpiDate)) and s.EmpKpiType=A.EmpKpiType and kpiType ='自评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K kpiscore=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )selfScore");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT SelfFile= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiFile FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID and (year(EmpKpiDate)=year(A.oldEmpKpiDate) and month(EmpKpiDate)=month(A.oldEmpKpiDate)) and s.EmpKpiType=A.EmpKpiType and kpiType ='自评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K KpiFile=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )selfFile");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT SelfTime= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT convert(varchar(19),k.CreateTime,121) CreateTime FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID and (year(EmpKpiDate)=year(A.oldEmpKpiDate) and month(EmpKpiDate)=month(A.oldEmpKpiDate)) and s.EmpKpiType=A.EmpKpiType and kpiType ='自评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K CreateTime=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )selfTime");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderID= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT CreatorID FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID  and (year(EmpKpiDate)=year(A.oldEmpKpiDate) and month(EmpKpiDate)=month(A.oldEmpKpiDate)) and s.EmpKpiType=A.EmpKpiType and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K CreatorID=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LearderID");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderScore= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT kpiscore   FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and (year(EmpKpiDate)=year(A.oldEmpKpiDate) and month(EmpKpiDate)=month(A.oldEmpKpiDate)) and s.EmpKpiType=A.EmpKpiType  and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N kpiscore=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )Learder");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderName= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT N.Creator   FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and (year(EmpKpiDate)=year(A.oldEmpKpiDate) and month(EmpKpiDate)=month(A.oldEmpKpiDate)) and s.EmpKpiType=A.EmpKpiType   and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N Creator=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LearderName");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderFile= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiFile FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and (year(EmpKpiDate)=year(A.oldEmpKpiDate) and month(EmpKpiDate)=month(A.oldEmpKpiDate)) and s.EmpKpiType=A.EmpKpiType  and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiFile=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LearderSelf ");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderTime= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT convert(varchar(19),N.CreateTime,121) CreateTime FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and (year(EmpKpiDate)=year(A.oldEmpKpiDate) and month(EmpKpiDate)=month(A.oldEmpKpiDate)) and s.EmpKpiType=A.EmpKpiType   and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N CreateTime=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LowerTime");
            sb.Append(" )E");
            sb.Append(" where 1=1");
            sb.Append(" and EmpKpiType='" + EmpKpiType + "'");
            sb.Append(strWhere);

            string totalSql = "select count(*) from (" + sb.ToString() + ") G";
            totalCount = long.Parse(DbHelperSQL.ExecuteScalar(totalSql).ToString());

            sbStr.Append(" select * from  (select top " + totalCount + " * from ( select top ");
            sbStr.Append(pageSize * pageIndex <= totalCount ? pageSize : totalCount - pageSize * (pageIndex - 1));
            sbStr.Append(" * from  (");
            sbStr.Append("  select * from (");
            sbStr.Append(sb.ToString());
            sbStr.Append(" ) B where rowindex not in(");
            sbStr.Append(" select top ");
            sbStr.Append((pageIndex - 1) * pageSize);
            sbStr.Append(" rowindex from (");
            sbStr.Append(sb.ToString());
            sbStr.Append(" )F order by EmpKpiDate desc)");
            sbStr.Append(" )G  order by EmpKpiDate desc)H order by EmpKpiDate desc");
            sbStr.Append(" )I order by EmpKpiDate desc");

            return DbHelperSQL.Query(sbStr.ToString());
        }

        /// <summary>
        /// 查询当前考核月度当前登录是否自评过...
        /// </summary>
        /// <param name="user"></param>
        /// <param name="KpiType"></param>
        /// <param name="EmpKpiType"></param>
        /// <param name="EmpKpiDate"></param>
        /// <returns></returns>
        public DataSet getEmpKpiDataMSelf(string empid, string userid, string KpiType, string EmpKpiType, string EmpKpiDate)
        {
            StringBuilder sb = new StringBuilder();
            DateTime dt = DateTime.Now;

            sb.Append(" select * from dbo.EmpKpiData k");
            sb.Append(" left join dbo.EmpKpiConfig c on k.EmpKpiConfigID=c.EmpKpiConfigID");
            sb.Append(" where KpiUserID='" + empid + "' and KpiType='" + KpiType + "' and  CreatorID='" + userid + "' and k.ValidMark=1 and EmpKpiType='" + EmpKpiType
                + "' and (year(EmpKpiDate)=" + dt.AddMonths(-1).Year + " and month(EmpKpiDate)=" + dt.AddMonths(-1).Month + ") ");

            return DbHelperSQL.Query(sb.ToString());
        }

        /// <summary>
        /// 查询当前考核年度当前登录是否自评过...
        /// </summary>
        /// <param name="user"></param>
        /// <param name="KpiType"></param>
        /// <param name="EmpKpiType"></param>
        /// <param name="EmpKpiDate"></param>
        /// <returns></returns>
        public DataSet getEmpKpiDataMSelf(string empid, string userid, string KpiType, string EmpKpiType, string EmpKpiDate, string EMPKPI_M, string EMPKPI_H, string EMPKPI_Y)
        {
            StringBuilder sb = new StringBuilder();

            DateTime dt = DateTime.Now;
            string strWhere = "";
            if (EmpKpiType == EMPKPI_H)//半年度
            {
                strWhere += "and (year(EmpKpiDate)=" + dt.AddMonths(-1).Year + " and month(EmpKpiDate)=7)";
            }
            else if (EmpKpiType == EMPKPI_Y)//年度
            {
                if (Convert.ToDateTime(EmpKpiDate).Month == 1)//如果是一月
                    strWhere += "and (year(EmpKpiDate)=" + dt.Year + " and month(EmpKpiDate)=1)";

                else
                    strWhere += "and (year(EmpKpiDate)=" + dt.AddYears(1).Year + " and month(EmpKpiDate)=1)";

            }

            sb.Append(" select * from dbo.EmpKpiData k");
            sb.Append(" left join dbo.EmpKpiConfig c on k.EmpKpiConfigID=c.EmpKpiConfigID");
            sb.Append(" where KpiUserID='" + empid + "' and KpiType='" + KpiType + "' and  CreatorID='" + userid + "' and k.ValidMark=1 and EmpKpiType='" + EmpKpiType
                + "'" + strWhere);

            return DbHelperSQL.Query(sb.ToString());
        }

        /// <summary>
        /// 查询当前考核度当前登录是否上评过...
        /// </summary>
        /// <param name="user"></param>
        /// <param name="KpiType"></param>
        /// <param name="EmpKpiType"></param>
        /// <param name="EmpKpiDate"></param>
        /// <returns></returns>
        public DataSet getEmpKpiDataMLearder(string empid, string userid, string KpiType, string EmpKpiType, string EmpKpiDate)
        {
            StringBuilder sb = new StringBuilder();
            DateTime dt = DateTime.Now;
            sb.Append(" select * from dbo.EmpKpiData k");
            sb.Append(" left join dbo.EmpKpiConfig c on k.EmpKpiConfigID=c.EmpKpiConfigID and EmpKpiType='" + EmpKpiType
                + "' and (year(EmpKpiDate)=" + dt.AddMonths(-1).Year + " and month(EmpKpiDate)=" + dt.AddMonths(-1).Month + ") ");
            sb.Append(" where KpiUserID='" + empid + "' and KpiType='" + KpiType + "' and  CreatorID='" + userid + "' and k.ValidMark=1");

            return DbHelperSQL.Query(sb.ToString());
        }

        /// <summary>
        /// 查询年度自评
        /// </summary>
        /// <param name="EmpKpiType"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="empName"></param>
        /// <param name="dept"></param>
        /// <param name="orderName"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataSet getEmpKpiDataY(string EmpKpiType, string EmpKpiDate, string empid, string createrid, string empName, string dept, long pageSize, long pageIndex, out long totalCount)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder sbStr = new StringBuilder();

            string strWhere = "";
            if (EmpKpiDate != "0")
            {
                strWhere += "and EmpKpiDate='" + EmpKpiDate + "'";
            }
            if (empid != "")
            {
                strWhere += "and KPIUSERID ='" + empid + "'";
            }
            if (createrid != "")
            {
                strWhere += "and selfID = '" + createrid + "'";
            }
            if (empName != "")
            {
                strWhere += "and KpiUser like '%" + empName + "%'";
            }
            if (dept != "")
            {
                strWhere += "and KpiUserDep like '%" + dept + "%'";
            }

            sb.Append(" select cast(row_number()  ");
            sb.Append("  over( order by KPIUSERID) ");
            sb.Append(" as int) rowindex,* from (");
            sb.Append(" SELECT * ");
            sb.Append(" FROM (SELECT DISTINCT KPIUSERID,d.EmpKpiConfigID,d.KpiUser,d.KpiUserDep,");
            sb.Append(" g.SelfMarkRate,g.LeaderMarkRate,g.EachMarkRate,g.LowerMarkRate,g.EmpKpiType,");
            sb.Append(" EmpKpiDate= case when month(g.EmpKpiDate)=1");
            sb.Append(" then convert(varchar(4),(year(g.EmpKpiDate)-1))+'年度'");
            sb.Append(" when month(g.EmpKpiDate)=7");
            sb.Append(" then convert(varchar(4),(year(g.EmpKpiDate)))+'上半年度'");
            sb.Append(" else ''");
            sb.Append(" end");
            sb.Append(" ,g.EmpKpiDate oldEmpKpiDate");
            sb.Append(" FROM EmpKpiData d");
            sb.Append(" left join dbo.EmpKpiConfig g on g.EmpKpiConfigID=d.EmpKpiConfigID");
            sb.Append(" where d.ValidMark=1 ");
            sb.Append(" ) A ");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT selfID= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT CreatorID FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType and kpiType ='自评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K CreatorID=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )selfID");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT SelfScore= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT kpiscore FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType and kpiType ='自评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K kpiscore=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )selfScore");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT SelfFile= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiFile FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType and kpiType ='自评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K KpiFile=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )selfFile");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT SelfTime= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT convert(varchar(19),k.CreateTime,121) CreateTime FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType and kpiType ='自评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K CreateTime=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )selfTime");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderScore= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT kpiscore   FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType  and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N kpiscore=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )Learder");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderName= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT N.Creator   FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N Creator=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LearderName");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderFile= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiFile FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType  and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiFile=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LearderSelf ");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderTime= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT convert(varchar(19),N.CreateTime,121) CreateTime FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N CreateTime=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LearderTime ");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT EachScore= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiScore FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='互评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiScore=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )EachScore");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT EachName= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT N.Creator   FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='互评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N Creator=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )EachName");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT EachFile= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiFile FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType  and kpiType ='互评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiFile=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )EachFile");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT EachTime= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT convert(varchar(19),N.CreateTime,121) CreateTime FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='互评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N CreateTime=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )EachTime");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LowerScore= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiScore FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='下评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiScore=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LowerScore");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LowerName= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT N.Creator   FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='下评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N Creator=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LowerName");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LowerFile= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiFile FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType  and kpiType ='下评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiFile=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LowerFile");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LowerTime= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT convert(varchar(19),N.CreateTime,121) CreateTime FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='下评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N CreateTime=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LowerTime");
            sb.Append(" )E");
            sb.Append(" where 1=1");
            sb.Append(" and EmpKpiType<>'" + EmpKpiType + "'");
            sb.Append(strWhere);

            string totalSql = "select count(*) from (" + sb.ToString() + ") G";
            totalCount = long.Parse(DbHelperSQL.ExecuteScalar(totalSql).ToString());

            sbStr.Append(" select * from  (select top " + totalCount + " * from ( select top ");
            sbStr.Append(pageSize * pageIndex <= totalCount ? pageSize : totalCount - pageSize * (pageIndex - 1));
            sbStr.Append(" * from  (");
            sbStr.Append("  select * from (");
            sbStr.Append(sb.ToString());
            sbStr.Append(" ) B where rowindex not in(");
            sbStr.Append(" select top ");
            sbStr.Append((pageIndex - 1) * pageSize);
            sbStr.Append(" rowindex from (");
            sbStr.Append(sb.ToString());
            sbStr.Append(" )F order by oldEmpKpiDate desc)");
            sbStr.Append(" )G  order by oldEmpKpiDate desc)H order by oldEmpKpiDate desc");
            sbStr.Append(" )I order by oldEmpKpiDate desc");

            return DbHelperSQL.Query(sbStr.ToString());
        }

        /// <summary>
        /// 查询年度当前登录人为别人的评分
        /// </summary>
        /// <param name="EmpKpiType"></param>
        /// <param name="EmpKpiDate"></param>
        /// <param name="empid"></param>
        /// <param name="createrid"></param>
        /// <param name="empName"></param>
        /// <param name="dept"></param>
        /// <param name="whereStr"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataSet getEmpKpiDataYOthers(string EmpKpiType, string EmpKpiDate, string empid, string createrid, string empName, string dept, string whereStr, long pageSize, long pageIndex, out long totalCount)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder sbStr = new StringBuilder();

            string strWhere = "";
            if (EmpKpiDate != "0")
            {
                strWhere += "and EmpKpiDate='" + EmpKpiDate + "'";
            }
            if (empName != "")
            {
                strWhere += "and KpiUser like '%" + empName + "%'";
            }
            if (dept != "")
            {
                strWhere += "and KpiUserDep like '%" + dept + "%'";
            }
            if (whereStr != "")
            {
                strWhere += whereStr;
            } 

            sb.Append(" select cast(row_number()  ");
            sb.Append("  over( order by KPIUSERID) ");
            sb.Append(" as int) rowindex,* from (");
            sb.Append(" SELECT * ");
            sb.Append(" FROM (SELECT DISTINCT KPIUSERID,d.EmpKpiConfigID,d.KpiUser,d.KpiUserDep,");
            sb.Append(" g.SelfMarkRate,g.LeaderMarkRate,g.EachMarkRate,g.LowerMarkRate,g.EmpKpiType,");
            sb.Append(" EmpKpiDate= case when month(g.EmpKpiDate)=1");
            sb.Append(" then convert(varchar(4),(year(g.EmpKpiDate)-1))+'年度'");
            sb.Append(" when month(g.EmpKpiDate)=7");
            sb.Append(" then convert(varchar(4),(year(g.EmpKpiDate)))+'上半年度'");
            sb.Append(" else ''");
            sb.Append(" end");
            sb.Append(" ,g.EmpKpiDate oldEmpKpiDate");
            sb.Append(" FROM EmpKpiData d");
            sb.Append(" left join dbo.EmpKpiConfig g on g.EmpKpiConfigID=d.EmpKpiConfigID");
            sb.Append(" where d.ValidMark=1 ");
            if (createrid != "")//给别人评分
            {
                sb.Append(" and CreatorID='" + createrid + "'");
            }
            sb.Append(" ) A ");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT SelfScore= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT kpiscore FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType and kpiType ='自评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K kpiscore=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )selfScore");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT SelfFile= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiFile FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType and kpiType ='自评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K KpiFile=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )selfFile");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT SelfTime= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT convert(varchar(19),k.CreateTime,121) CreateTime FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType and kpiType ='自评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K CreateTime=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )selfTime");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderScore= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT kpiscore   FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType  and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N kpiscore=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )Learder");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderName= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT N.Creator   FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N Creator=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LearderName");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderFile= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiFile FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType  and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiFile=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LearderSelf ");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderTime= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT convert(varchar(19),N.CreateTime,121) CreateTime FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N CreateTime=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LearderTime ");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT EachScore= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiScore FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='互评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiScore=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )EachScore");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT EachName= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT N.Creator   FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='互评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N Creator=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )EachName");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT EachFile= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiFile FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType  and kpiType ='互评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiFile=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )EachFile");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT EachTime= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT convert(varchar(19),N.CreateTime,121) CreateTime FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='互评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N CreateTime=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )EachTime");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LowerScore= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiScore FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='下评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiScore=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LowerScore");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LowerName= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT N.Creator   FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='下评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N Creator=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LowerName");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LowerFile= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiFile FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType  and kpiType ='下评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiFile=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LowerFile");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LowerTime= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT convert(varchar(19),N.CreateTime,121) CreateTime FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='下评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N CreateTime=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LowerTime");
            sb.Append(" )E");
            sb.Append(" where 1=1");
            sb.Append(" and EmpKpiType <>'" + EmpKpiType + "'");
            sb.Append(strWhere);

            string totalSql = "select count(*) from (" + sb.ToString() + ") G";
            totalCount = long.Parse(DbHelperSQL.ExecuteScalar(totalSql).ToString());

            sbStr.Append(" select * from  (select top " + totalCount + " * from ( select top ");
            sbStr.Append(pageSize * pageIndex <= totalCount ? pageSize : totalCount - pageSize * (pageIndex - 1));
            sbStr.Append(" * from  (");
            sbStr.Append("  select * from (");
            sbStr.Append(sb.ToString());
            sbStr.Append(" ) B where rowindex not in(");
            sbStr.Append(" select top ");
            sbStr.Append((pageIndex - 1) * pageSize);
            sbStr.Append(" rowindex from (");
            sbStr.Append(sb.ToString());
            sbStr.Append(" )F order by oldEmpKpiDate desc)");
            sbStr.Append(" )G  order by oldEmpKpiDate desc)H order by oldEmpKpiDate desc");
            sbStr.Append(" )I order by oldEmpKpiDate desc");

            return DbHelperSQL.Query(sbStr.ToString());
        }

        /// <summary>
        /// 获取可查询考核期
        /// </summary>
        /// <param name="EmpKpiType"></param>
        /// <returns></returns>
        public DataSet getEmpKpiDate(string EmpKpiType)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(" SELECT DISTINCT d.EmpKpiConfigID,");
            sb.Append(" EmpKpiDate= case when month(g.EmpKpiDate)=1");
            sb.Append(" then convert(varchar(4),(year(g.EmpKpiDate)-1))+'年度'");
            sb.Append(" when month(g.EmpKpiDate)=7");
            sb.Append(" then convert(varchar(4),(year(g.EmpKpiDate)))+'上半年度'");
            sb.Append(" else ''");
            sb.Append(" end");
            sb.Append(" FROM EmpKpiData d");
            sb.Append(" left join dbo.EmpKpiConfig g on g.EmpKpiConfigID=d.EmpKpiConfigID");
            sb.Append(" where d.ValidMark=1 and EmpKpiType <>'" + EmpKpiType + "' order by EmpKpiDate desc");


            return DbHelperSQL.Query(sb.ToString());
        }

        /// <summary>
        /// 导出月度的Excel
        /// </summary>
        /// <param name="EmpKpiType"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public DataSet ExportExcelM(string EmpKpiType, string year, string month)
        {
            StringBuilder sb = new StringBuilder();

            string strWhere = "";
            if (year != "")
            {
                strWhere += "and year(oldEmpKpiDate)='" + year + "'";
            }
            if (month != "")
            {
                strWhere += "and month(oldEmpKpiDate)='" + month + "'";
            }

            sb.Append(" select cast(row_number()  ");
            sb.Append("  over( order by KPIUSERID) ");
            sb.Append(" as int) rowindex,* from (");
            sb.Append(" SELECT * ");
            sb.Append(" FROM (SELECT DISTINCT KPIUSERID,d.EmpKpiConfigID,d.KpiUser,d.KpiUserDep,KeyValue,");
            sb.Append(" g.SelfMarkRate,g.LeaderMarkRate,g.EmpKpiType,convert(varchar(7),EmpKpiDate,121) EmpKpiDate ,g.EmpKpiDate oldEmpKpiDate");
            sb.Append(" FROM EmpKpiData d");
            sb.Append(" left join dbo.EmpKpiConfig g on g.EmpKpiConfigID=d.EmpKpiConfigID");
            sb.Append(" left join dbo.EmpPersonBase e on d.KpiUserID=e.PersonID");
            sb.Append(" left join dbo.SysDictionarySub s on e.PersonType=s.ID");
            sb.Append(" where d.ValidMark=1 ");
            sb.Append(" ) A ");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT SelfScore= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT kpiscore FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID and (year(EmpKpiDate)=year(A.oldEmpKpiDate) and month(EmpKpiDate)=month(A.oldEmpKpiDate))  and s.EmpKpiType=A.EmpKpiType and kpiType ='自评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K kpiscore=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )selfScore");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT SelfFile= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiFile FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID and (year(EmpKpiDate)=year(A.oldEmpKpiDate) and month(EmpKpiDate)=month(A.oldEmpKpiDate)) and s.EmpKpiType=A.EmpKpiType and kpiType ='自评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K KpiFile=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )selfFile");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT SelfTime= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT convert(varchar(19),k.CreateTime,121) CreateTime FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID and (year(EmpKpiDate)=year(A.oldEmpKpiDate) and month(EmpKpiDate)=month(A.oldEmpKpiDate)) and s.EmpKpiType=A.EmpKpiType and kpiType ='自评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K CreateTime=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )selfTime");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderScore= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT kpiscore   FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID and (year(EmpKpiDate)=year(A.oldEmpKpiDate) and month(EmpKpiDate)=month(A.oldEmpKpiDate)) and s.EmpKpiType=A.EmpKpiType  and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N kpiscore=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )Learder");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderName= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT N.Creator   FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID and (year(EmpKpiDate)=year(A.oldEmpKpiDate) and month(EmpKpiDate)=month(A.oldEmpKpiDate)) and s.EmpKpiType=A.EmpKpiType   and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N Creator=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LearderName");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderFile= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiFile FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID and (year(EmpKpiDate)=year(A.oldEmpKpiDate) and month(EmpKpiDate)=month(A.oldEmpKpiDate)) and s.EmpKpiType=A.EmpKpiType  and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiFile=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LearderSelf ");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderTime= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT convert(varchar(19),N.CreateTime,121) CreateTime FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID and (year(EmpKpiDate)=year(A.oldEmpKpiDate) and month(EmpKpiDate)=month(A.oldEmpKpiDate)) and s.EmpKpiType=A.EmpKpiType   and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N CreateTime=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LowerTime");
            sb.Append(" )E");
            sb.Append(" where 1=1");
            sb.Append(" and EmpKpiType='" + EmpKpiType + "'");
            sb.Append(strWhere);
            sb.Append(" order by KpiUserDep,EmpKpiDate desc");


            return DbHelperSQL.Query(sb.ToString());
        }

        /// <summary>
        /// 导出年度考核
        /// </summary>
        /// <param name="EmpKpiType"></param>
        /// <param name="EmpKpiDate"></param>
        /// <returns></returns>
        public DataSet ExportExcelY(string EmpKpiType, string EmpKpiDate)
        {
            StringBuilder sb = new StringBuilder();

            string strWhere = "";
            if (EmpKpiDate != "")
            {
                strWhere += "and EmpKpiDate='" + EmpKpiDate + "'";
            }

            sb.Append(" select cast(row_number()  ");
            sb.Append("  over( order by KPIUSERID) ");
            sb.Append(" as int) rowindex,* from (");
            sb.Append(" SELECT * ");
            sb.Append(" FROM (SELECT DISTINCT KPIUSERID,d.EmpKpiConfigID,d.KpiUser,d.KpiUserDep,KeyValue,");
            sb.Append(" g.SelfMarkRate,g.LeaderMarkRate,g.EachMarkRate,g.LowerMarkRate,g.EmpKpiType,");
            sb.Append(" EmpKpiDate= case when month(g.EmpKpiDate)=1");
            sb.Append(" then convert(varchar(4),(year(g.EmpKpiDate)-1))+'年度'");
            sb.Append(" when month(g.EmpKpiDate)=7");
            sb.Append(" then convert(varchar(4),(year(g.EmpKpiDate)))+'上半年度'");
            sb.Append(" else ''");
            sb.Append(" end");
            sb.Append(" ,g.EmpKpiDate oldEmpKpiDate");
            sb.Append(" FROM EmpKpiData d");
            sb.Append(" left join dbo.EmpKpiConfig g on g.EmpKpiConfigID=d.EmpKpiConfigID");
            sb.Append(" left join dbo.EmpPersonBase e on d.KpiUserID=e.PersonID");
            sb.Append(" left join dbo.SysDictionarySub s on e.PersonType=s.ID");
            sb.Append(" where d.ValidMark=1 ");
            sb.Append(" ) A ");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT SelfScore= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT kpiscore FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType and kpiType ='自评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K kpiscore=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )selfScore");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT SelfFile= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiFile FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType and kpiType ='自评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K KpiFile=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )selfFile");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT SelfTime= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT convert(varchar(19),k.CreateTime,121) CreateTime FROM EmpKpiData K left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=k.EmpKpiConfigID");
            sb.Append(" WHERE k.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType and kpiType ='自评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<K CreateTime=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )selfTime");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderScore= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT kpiscore   FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType  and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N kpiscore=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )Learder");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderName= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT N.Creator   FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N Creator=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LearderName");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderFile= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiFile FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType  and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiFile=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LearderSelf ");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LearderTime= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT convert(varchar(19),N.CreateTime,121) CreateTime FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='上评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N CreateTime=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LearderTime ");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT EachScore= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiScore FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='互评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiScore=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )EachScore");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT EachName= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT N.Creator   FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='互评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N Creator=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )EachName");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT EachFile= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiFile FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType  and kpiType ='互评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiFile=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )EachFile");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT EachTime= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT convert(varchar(19),N.CreateTime,121) CreateTime FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='互评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N CreateTime=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )EachTime");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LowerScore= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiScore FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='下评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiScore=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LowerScore");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LowerName= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT N.Creator   FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='下评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N Creator=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LowerName");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LowerFile= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT KpiFile FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType  and kpiType ='下评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N KpiFile=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LowerFile");
            sb.Append(" OUTER APPLY( ");
            sb.Append(" SELECT LowerTime= STUFF(REPLACE(REPLACE( ");
            sb.Append(" ( ");
            sb.Append(" SELECT convert(varchar(19),N.CreateTime,121) CreateTime FROM EmpKpiData N left join dbo.EmpKpiConfig s on s.EmpKpiConfigID=n.EmpKpiConfigID");
            sb.Append(" WHERE n.KPIUSERID = A.KPIUSERID  and s.EmpKpiType=A.EmpKpiType   and kpiType ='下评'");
            sb.Append(" FOR XML AUTO ");
            sb.Append(" ), '<N CreateTime=\"', ','), '\"/>', ''), 1, 1, '')    ");
            sb.Append(" )LowerTime");
            sb.Append(" )E");
            sb.Append(" where 1=1");
            sb.Append(" and EmpKpiType <>'" + EmpKpiType + "'");
            sb.Append(strWhere);
            sb.Append(" order by KpiUserDep,oldEmpKpiDate desc");

            return DbHelperSQL.Query(sb.ToString());
        }

    }
}

